﻿/***********************************************************************************************************************************
 *    Name: Query_Performance.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
 *	http://www.brentozar.com/archive/2014/04/collecting-detailed-performance-measurements-extended-events/
**/
-- execute on monitored server
USE master;
DECLARE
  @action		NVARCHAR(MAX)= N'sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.plan_handle, sqlserver.username, sqlserver.session_id, sqlserver.sql_text',
  @where		NVARCHAR(MAX)= N'cpu > 0 OR reads > 0 OR writes > 0',
  @target		NVARCHAR(MAX)= N'package0.asynchronous_file_target',
  @xe_create	NVARCHAR(MAX);

IF 11.0 <= master.DBAdmin.ProductVersion() BEGIN
  SET @action	= N'package0.event_sequence, ' + REPLACE(@action, N'sqlserver.database_id', N'sqlserver.database_name') + N', sqlserver.query_hash, sqlserver.query_plan_hash';
  SET @where	= N'cpu_time > 0 OR logical_reads > 0 OR writes > 0';
  SET @target	= N'package0.event_file';
END;

SET @xe_create= N'CREATE EVENT SESSION Query_Performance ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
	ACTION (' + @action + N')
	WHERE (sqlserver.is_system = 0 AND (' + @where + N'))),
ADD EVENT sqlserver.rpc_completed (
	ACTION (' + @action + N')
	WHERE (sqlserver.is_system = 0 AND (' + @where + N'))),
ADD EVENT sqlos.wait_info (
	ACTION (' + @action + N')
	WHERE (sqlserver.is_system = 0 AND duration > 0))
ADD TARGET ' + @target + N' (
	SET filename= N''' + master.DBAdmin.GetInstancePath(N'ErrorDumpDir') + N'Query_Performance.xel'', max_file_size= 1024, max_rollover_files= 128)
WITH (EVENT_RETENTION_MODE= ALLOW_MULTIPLE_EVENT_LOSS);';
PRINT @xe_create;

BEGIN TRY DROP EVENT SESSION Query_Performance ON SERVER END TRY BEGIN CATCH END CATCH;
EXECUTE (@xe_create);
ALTER EVENT SESSION Query_Performance ON SERVER STATE= START;
GO


/***********************************************************************************************************************************
CREATE VIEW dbo.Query_Performance AS SELECT
	instance_name,
	timestamp		= event_data.value('(/event/       @timestamp                    )[1]', 'DATETIME2(3)'	),
	name			= event_data.value('(/event/       @name                         )[1]', 'SYSNAME'		),
	package			= event_data.value('(/event/       @package                      )[1]', 'SYSNAME'		),
	client_hostname	= event_data.value('(/event/action[@name="client_hostname"]/value)[1]', 'NVARCHAR(128)'	),
	client_app_name	= event_data.value('(/event/action[@name="client_app_name"]/value)[1]', 'NVARCHAR(128)'	),
	username		= event_data.value('(/event/action[@name="username"       ]/value)[1]', 'NVARCHAR(128)'	),
	duration		= event_data.value('(/event/data  [@name="duration"       ]/value)[1]', 'BIGINT'		),
	cpu_time		= event_data.value('(/event/data  [@name="cpu_time"       ]/value)[1]', 'BIGINT'		),
	reads			= event_data.value('(/event/data  [@name="logical_reads"  ]/value)[1]', 'BIGINT'		),
	writes			= event_data.value('(/event/data  [@name="writes"         ]/value)[1]', 'BIGINT'		),
	database_name	= event_data.value('(/event/action[@name="database_name"  ]/value)[1]', 'SYSNAME'		),
	statement		= event_data.value('(/event/data  [@name="statement"      ]/value)[1]', 'NVARCHAR(MAX)'	),
	query_hash		= event_data.value('(/event/action[@name="query_hash"     ]/value)[1]', 'DECIMAL(20,0)'	)
  FROM dbo.event_file;
GO

SELECT
	instance_name,
	timestamp,
	package_name,
	event_name,
	event_sequence	= event_data.value('(/event/action[@name="event_sequence" ]/value)[1]', 'INT'			),
	client_app_name	= event_data.value('(/event/action[@name="client_app_name"]/value)[1]', 'NVARCHAR(128)'	),
	client_hostname	= event_data.value('(/event/action[@name="client_hostname"]/value)[1]', 'NVARCHAR(128)'	),
	database_name	= event_data.value('(/event/action[@name="database_name"  ]/value)[1]', 'NVARCHAR(128)'	),
	database_id		= event_data.value('(/event/action[@name="database_id  "  ]/value)[1]', 'NVARCHAR(5)'	),
	ISNULL(event_data.value('(/event/action[@name="database_name"  ]/value)[1]', 'NVARCHAR(128)'	), event_data.value('(/event/action[@name="database_id  "  ]/value)[1]', 'NVARCHAR(5)'	)),
	plan_handle		= event_data.value('(/event/action[@name="plan_handle"    ]/value)[1]', 'NVARCHAR(128)'	),
	session_id		= event_data.value('(/event/action[@name="session_id"     ]/value)[1]', 'SMALLINT'		),
	sql_text		= event_data.value('(/event/action[@name="sql_text"       ]/value)[1]', 'NVARCHAR(MAX)'	),
	query_hash		= event_data.value('(/event/action[@name="query_hash"     ]/value)[1]', 'DECIMAL(20,0)'	),
	query_plan_hash	= event_data.value('(/event/action[@name="query_plan_hash"]/value)[1]', 'DECIMAL(20,0)'	)
  FROM xe_file


SELECT
	instance_name, query_hash,
	SUM(duration)/1000 AS duration_ms,
	SUM(cpu_time)/1000 AS cpu_time_ms,
	SUM(reads) AS reads,
	SUM(writes) AS writes
  FROM dbo.Query_Performance
  GROUP BY instance_name, query_hash
  ORDER BY duration_ms DESC;
*/